//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using CommunityServer.Components;
using CommunityServer.RollerBlogs.Components;

namespace CommunityServer.RollerBlogs.Data
{
    public class RollerBlogsSqlDataProvider : RollerBlogsDataProvider
    {

		#region Member variables
		protected string databaseOwner	= "dbo";	// overwrite in web.config
		string connectionString = null;
		ProviderHelper sqlHelper = null;
		#endregion

		#region Constructor
        public RollerBlogsSqlDataProvider(string databaseOwner, string connectionString) 
		{

			// Read the connection string for this provider
			//
			this.connectionString = connectionString;

			// Read the database owner name for this provider
			//
			this.databaseOwner = databaseOwner;

            sqlHelper = ProviderHelper.Instance();

		}
		#endregion
	
		#region SettingsID 
        protected int GetSettingsID()
        {
            return  sqlHelper.GetSettingsID();
        }
		protected SqlParameter SettingsIDParameter()
		{
			SqlParameter p = new SqlParameter("@SettingsID",SqlDbType.Int);
			p.Value = GetSettingsID();
			return p;
		}
		#endregion

		#region Helpers
		protected SqlConnection GetSqlConnection () 
		{

			try 
			{
				return new SqlConnection(ConnectionString);
			} 
			catch 
			{
				throw new CSException(CSExceptionType.DataProvider, "SQL Connection String is invalid.");
			}

		}

		public string ConnectionString 
		{
			get 
			{
				return connectionString;
			}
			set 
			{
				connectionString = value;
			}
		}
		#endregion

        /// <summary>
        /// Returns an ArrayList of RollerBlogFeeds from the database
        /// </summary>
        /// <param name="enabledOnly">include only feeds that are enabled (disabled feeds are deleted feeds)</param>
        /// <param name="updateNeededOnly">include only feeds that have not been updated by their next scheduled update time</param>
        /// <param name="SectionID">include only feeds for the specified sectionid (0 = ignore)</param>
        /// <returns></returns>
        public override ArrayList GetFeeds(bool enabledOnly, bool updateNeededOnly, int SectionID)
        {
            //Create Instance of Connection and Command Object

            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RollerBlog_GetFeeds", myConnection);
                ArrayList Feeds = new ArrayList();

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Pass sproc parameters
                myCommand.Parameters.Add(this.SettingsIDParameter());
                
                if(SectionID != 0)
                    myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = SectionID;
                
                if(enabledOnly)
                    myCommand.Parameters.Add("@EnabledOnly", SqlDbType.Bit).Value = enabledOnly;
                
                if(updateNeededOnly)
                    myCommand.Parameters.Add("@UpdateNeededOnly", SqlDbType.Bit).Value = updateNeededOnly;

                // Execute the command
                //
                myConnection.Open();
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    // Populate the ArrayList with feeds
                    //
                    while (dr.Read())
                        Feeds.Add(PopulateRollerBlogFeedFromIDataReader(dr));

                    dr.Close();
                }
                myCommand.Dispose();
                myConnection.Close();

                return Feeds;
            }
        }


        
        public override ArrayList GetRollerBlogPosts(RollerBlogFeed rbf)
        {
            //Create Instance of Connection and Command Object

            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RollerBlog_GetPosts", myConnection);
                ArrayList Posts = new ArrayList();

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Pass sproc parameters
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = rbf.SectionId;
                myCommand.Parameters.Add("@UrlId", SqlDbType.Int).Value = rbf.UrlId;

                // Execute the command
                //
                myConnection.Open();
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    // Populate the ArrayList with feeds
                    //
                    while (dr.Read())
                        Posts.Add(PopulateRollerBlogPostFromIDataReader(dr));

                    dr.Close();
                }
                myCommand.Dispose();
                myConnection.Close();

                return Posts;
            }
        }

        /// <summary>
        /// Save the state flag and updates the last update to the SQL Servers datetime
        /// </summary>
        /// <param name="rbf"></param>
        public override DateTime UpdateRollerBlogFeed(RollerBlogFeed rbf)
        {
            // Create Instance of Connection and Command Object
            //
            using (SqlConnection myConnection = GetSqlConnection())
            {

                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RollerBlog_UpdateFeed", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Set parameters
                //
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = rbf.SectionId;
                myCommand.Parameters.Add("@UrlId", SqlDbType.Int).Value = rbf.UrlId;
                myCommand.Parameters.Add("@FeedState", SqlDbType.Int).Value = Convert.ToInt32(rbf.State);

                SqlParameter p = new SqlParameter("@LastUpdateDate", SqlDbType.DateTime);
                p.Direction = ParameterDirection.Output;
                myCommand.Parameters.Add(p);
                
                // Execute the command
                //
                myConnection.Open();
                myCommand.ExecuteNonQuery();

                DateTime lastUpdateDate = DateTime.Parse(myCommand.Parameters["@LastUpdateDate"].Value.ToString());
                
                myConnection.Close();
                myCommand.Dispose();

                return lastUpdateDate;
            }
        }

        public override int AddRollerBlogFeed(RollerBlogFeed rbf)
        {
            // Create Instance of Connection and Command Object
            //
            using (SqlConnection myConnection = GetSqlConnection())
            {

                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RollerBlog_AddFeed", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Set parameters
                //
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = rbf.SectionId;
                myCommand.Parameters.Add("@FeedUrl", SqlDbType.NVarChar).Value = rbf.Url;
                myCommand.Parameters.Add("@SiteUrl", SqlDbType.NVarChar).Value = rbf.SiteUrl;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = rbf.Title;
                myCommand.Parameters.Add("@IntervalMin", SqlDbType.Int).Value = rbf.IntervalMinutes;
                myCommand.Parameters.Add("@ExerptSize", SqlDbType.Int).Value = rbf.ExerptSize;
                myCommand.Parameters.Add("@PostFullText", SqlDbType.Bit).Value = rbf.PostFullText;
                myCommand.Parameters.Add("@IsBlogAggregated", SqlDbType.Bit).Value = rbf.IsBlogAggregated;
                myCommand.Parameters.Add("@IsBlogRollAggregated", SqlDbType.Bit).Value = rbf.IsBlogRollAggregated;
                myCommand.Parameters.Add("@Enabled", SqlDbType.Bit).Value = rbf.Enabled;
                myCommand.Parameters.Add("@State", SqlDbType.Int).Value = rbf.State;

                myCommand.Parameters.Add(this.SettingsIDParameter());
                
                SqlParameter p = new SqlParameter("@NewUrlID", SqlDbType.Int, 4);
                p.Direction = ParameterDirection.Output;
                myCommand.Parameters.Add(p);

                // Execute the command
                //
                myConnection.Open();
                myCommand.ExecuteNonQuery();

                int newUrlID = (int)myCommand.Parameters["@NewUrlID"].Value;
                myConnection.Close();
                myCommand.Dispose();

                return newUrlID;
            }
        }

        public override void DeleteRollerBlogFeed(RollerBlogFeed rbf)
        {
            // Create Instance of Connection and Command Object
            //
            using (SqlConnection myConnection = GetSqlConnection())
            {

                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RollerBlog_DeleteFeed", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Set parameters
                //
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = rbf.SectionId;
                myCommand.Parameters.Add("@UrlId", SqlDbType.Int).Value = rbf.UrlId;

                // Execute the command
                //
                myConnection.Open();
                myCommand.ExecuteNonQuery();

                myConnection.Close();
                myCommand.Dispose();
            }
        }

        public override void AddRollerBlogPost(RollerBlogPost rbp)
        {
            // Create Instance of Connection and Command Object
            //
            using (SqlConnection myConnection = GetSqlConnection())
            {

                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RollerBlog_AddPost", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Set parameters
                //
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = rbp.PostID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = rbp.SectionId;
                myCommand.Parameters.Add("@URlId", SqlDbType.Int).Value = rbp.UrlId;
                myCommand.Parameters.Add("@PermaLink", SqlDbType.NVarChar).Value = rbp.PermaLink;
                myCommand.Parameters.Add("@CommentsUrl", SqlDbType.NVarChar).Value = rbp.CommentsUrl;
                myCommand.Parameters.Add("@CommentsCount", SqlDbType.Int).Value = rbp.CommentCount;
                myCommand.Parameters.Add("@GuidName", SqlDbType.NVarChar).Value = rbp.GuidName;
                myCommand.Parameters.Add("@GuidIsPermaLink", SqlDbType.Bit).Value = rbp.GuidIsPermaLink;

                // Execute the command
                //
                myConnection.Open();
                myCommand.ExecuteNonQuery();

                myConnection.Close();
                myCommand.Dispose();
            }
        }

        public override void UpdateRollerBlogPost(RollerBlogPost rbp)
        {
            // Create Instance of Connection and Command Object
            //
            using (SqlConnection myConnection = GetSqlConnection())
            {

                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RollerBlog_UpdatePost", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Set parameters
                //
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = rbp.PostID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = rbp.SectionId;
                myCommand.Parameters.Add("@URlId", SqlDbType.Int).Value = rbp.UrlId;
                myCommand.Parameters.Add("@PermaLink", SqlDbType.NVarChar).Value = rbp.PermaLink;
                myCommand.Parameters.Add("@CommentsUrl", SqlDbType.NVarChar).Value = rbp.CommentsUrl;
                myCommand.Parameters.Add("@CommentsCount", SqlDbType.Int).Value = rbp.CommentCount;
                myCommand.Parameters.Add("@GuidName", SqlDbType.NVarChar).Value = rbp.GuidName;
                myCommand.Parameters.Add("@GuidIsPermaLink", SqlDbType.Bit).Value = rbp.GuidIsPermaLink;

                // Execute the command
                //
                myConnection.Open();
                myCommand.ExecuteNonQuery();

                myConnection.Close();
                myCommand.Dispose();
            }
        }


    }
}
